package com.xuwang.Util;

import com.xuwang.pojo.User;
import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import org.springframework.util.FileCopyUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @ Author  460503919
 * @ Date    2021/9/18 9:08
 * @ VERSION 1.0.0
 */
public class ExcelImage {
    //上传文件到本地
    public static String uploadExcel(MultipartFile file, String path, HttpServletRequest request) throws IOException {
        String uploadPath = request.getServletContext().getRealPath("") + File.separator + path + "\\";
        String fileName = file.getOriginalFilename();
        File newFile = new File(uploadPath + fileName);
        FileCopyUtils.copy(file.getBytes(), newFile);
        return path+"\\"+fileName;
    }



    //    获取用户信息
    public static List<User> getUserInfos(String path,HttpServletRequest request) throws IOException, InvalidFormatException {
        String uploadPath = request.getServletContext().getRealPath("") + File.separator +path;
        File file = new File(uploadPath);
        // 创建流
        InputStream input = new FileInputStream(file);
        // 获取文件后缀名
        String fileExt = file.getName().substring(file.getName().lastIndexOf(".") + 1);
        // 创建Workbook
        Workbook wb = null;
        // 创建sheet
        Sheet sheet = null;
        //根据后缀判断excel 2003 or 2007+
        if (fileExt.equals("xls")) {
            wb = (HSSFWorkbook) WorkbookFactory.create(input);
        } else {
            wb = new XSSFWorkbook(input);
        }
        //获取excel sheet总数
        int sheetNumbers = wb.getNumberOfSheets();

        // sheet list
        List<Map<String, PictureData>> sheetList = new ArrayList<Map<String, PictureData>>();
        // 循环sheet
        for (int i = 0; i < sheetNumbers; i++) {
            sheet = wb.getSheetAt(i);
            // map等待存储excel图片
            Map<String, PictureData> sheetIndexPicMap;
            // 判断用07还是03的方法获取图片
            if (fileExt.equals("xls")) {
                sheetIndexPicMap = getSheetPictrues03(i, (HSSFSheet) sheet, (HSSFWorkbook) wb);
            } else {
                sheetIndexPicMap = getSheetPictrues07(i, (XSSFSheet) sheet, (XSSFWorkbook) wb);
            }
            // 将当前sheet图片map存入list
            sheetList.add(sheetIndexPicMap);
        }
        //图片路径
        List<String> imgList = printImg(sheetList,request);
        //创建对象列表
        List<User> userList = new ArrayList<>();
        // 判断用07还是03的方法获取单元格信息
        if (fileExt.equals("xls")) {

        } else {

            User user;
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                user = new User();
                user.setUsername(row.getCell(0).getStringCellValue());
                user.setPassword((int)row.getCell(1).getNumericCellValue() + "");
                user.setProfession(row.getCell(2).getStringCellValue());
                //改变婚姻状况和性别值
                String married = row.getCell(3).getStringCellValue();
                if ("已婚".equals(married)) {
                    user.setMarried(1);
                }else {
                    user.setMarried(0);
                }
                String sex = row.getCell(4).getStringCellValue();
                if ("男".equals(sex)){
                    user.setSex(1);
                }else {
                    user.setSex(0);
                }
                user.setPhone((int)row.getCell(5).getNumericCellValue()+"");
                user.setEmail(row.getCell(6).getStringCellValue());
                user.setImg("../upload/"+imgList.get(i - 1));
                userList.add(user);
            }
        }
        return userList;
    }

    /**
     * 获取Excel2003图片
     *
     * @param sheetNum 当前sheet编号
     * @param sheet    当前sheet对象
     * @param workbook 工作簿对象
     * @return Map key:图片单元格索引（0_1_1）String，value:图片流PictureData
     * @throws IOException
     */
    public static Map<String, PictureData> getSheetPictrues03(int sheetNum, HSSFSheet sheet, HSSFWorkbook workbook) {
        Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
        List<HSSFPictureData> pictures = workbook.getAllPictures();
        if (pictures.size() != 0) {
            for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {
                HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();

                if (shape instanceof HSSFPicture) {
                    HSSFPicture pic = (HSSFPicture) shape;
                    int pictureIndex = pic.getPictureIndex() - 1;
                    HSSFPictureData picData = pictures.get(pictureIndex);
                    String picIndex = String.valueOf(sheetNum) + "_"
                            + String.valueOf(anchor.getRow1()) + "_"
                            + String.valueOf(anchor.getCol1());
                    sheetIndexPicMap.put(picIndex, picData);
                }
            }
            return sheetIndexPicMap;
        } else {
            return null;
        }
    }

    /**
     * 获取Excel2007图片
     *
     * @param sheetNum 当前sheet编号
     * @param sheet    当前sheet对象
     * @param workbook 工作簿对象
     * @return Map key:图片单元格索引（0_1_1）String，value:图片流PictureData
     */
    public static Map<String, PictureData> getSheetPictrues07(int sheetNum, XSSFSheet sheet, XSSFWorkbook workbook) {
        Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
        int i = 0;
        for (POIXMLDocumentPart dr : sheet.getRelations()) {

            if (dr instanceof XSSFDrawing) {
                XSSFDrawing drawing = (XSSFDrawing) dr;
                List<XSSFShape> shapes = drawing.getShapes();
                for (XSSFShape shape : shapes) {

                    XSSFPicture pic = (XSSFPicture) shape;
                    XSSFClientAnchor anchor = pic.getPreferredSize();
                    CTMarker ctMarker = anchor.getFrom();
                    String picIndex = String.valueOf(sheetNum) + "_"
                            + ctMarker.getRow() + "_" + ctMarker.getCol();
                    sheetIndexPicMap.put(picIndex, pic.getPictureData());
                }
            }
        }
        return sheetIndexPicMap;
    }

    //上传图片保存路径
    public static List<String> printImg(List<Map<String, PictureData>> sheetList,HttpServletRequest request) throws IOException {
        List<String> pathList = new ArrayList<>();
        for (Map<String, PictureData> map : sheetList) {
            Object key[] = map.keySet().toArray();
            for (int i = 0; i < map.size(); i++) {
                // 获取图片流
                PictureData pic = map.get(key[i]);
                // 获取图片索引
                String picName = key[i].toString();

                System.out.println(picName);
                // 获取图片格式
                String ext = pic.suggestFileExtension();
                //获取图片路径
                pathList.add(picName+"."+ext);
                byte[] data = pic.getData();
                FileOutputStream out = new FileOutputStream(request.getServletContext().getRealPath("") + File.separator +"upload\\" + picName + "." + ext);
                out.write(data);
                out.close();
            }

        }
        return pathList;
    }
}
